package Dao;

import DBCPUtil.DBCPUtil;
import Dto.Goods;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class GoodsDao {
    QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
    //列表 分页
    public List<Goods> selectCategoryList(Integer pageNum) throws SQLException {
        String sql = "select * from goods limit ?,?";
        List<Goods> list = qr.query(sql, new BeanListHandler<>(Goods.class), (pageNum-1)*10, 10);
        return list;
    }
    //数据条数
    public Long selectCategoryCount() throws SQLException {
        String sql = "select count(*) from goods";
        Long count = qr.query(sql, new ScalarHandler<>());
        return count;
    }
    //商品增加
    public void addGoods(Goods goods) throws SQLException {
        String sql = "insert into goods values(null,?,?,?,?,?,?,?,?,?,?)";
        qr.update(sql, goods.getName(), goods.getGoodscategory(), goods.getDescribe(), goods.getPicture(), goods.getOldprice(),
                goods.getNewprice(), goods.getSalenumber(), goods.getInventory(), goods.getStatus(), goods.getAddtime());
    }
    //商品预修改
    public Goods preUpdateGoods(Integer id) throws SQLException {
        String sql = "select * from goods where id = ?";
        Goods goods = qr.query(sql, new BeanHandler<>(Goods.class), id);
        return goods;
    }
    //商品修改
    public void updateGoods(Goods goods) throws SQLException {
        String sql = "update goods set name=?,goodscategory=?,describe=?,picture=?,oldprice=?,newprice=?,salenumber=?,inventory=?,status=?,addtime=? where id=?";
        qr.update(sql, goods.getName(), goods.getGoodscategory(), goods.getDescribe(), goods.getPicture(), goods.getOldprice(),
                goods.getNewprice(), goods.getSalenumber(), goods.getInventory(), goods.getStatus(), goods.getAddtime(),
                goods.getId());
    }
    //商品删除
    public void deleteGoods(Integer id) throws SQLException {
        String sql = "delete from goods where id=?";
        qr.update(sql, id);
    }
    //商品名称模糊查询
    public List<Goods> selectGoodsName(String name) throws SQLException {
        String sql = "select * from goods where name like ?";
        List<Goods> list = qr.query(sql, new BeanListHandler<>(Goods.class), "%"+name+"%");
        return list;
    }
    //商品类别查询
    public List<Goods> selectGoodsCategory(String category) throws SQLException {
        String sql = "select * from goods where goodscategory = ?";
        List<Goods> list = qr.query(sql, new BeanListHandler<>(Goods.class), category);
        return list;
    }
    //商品状态查询
    public List<Goods> selectGoodsState(String status) throws SQLException {
        String sql = "select * from goods where status = ?";
        List<Goods> list = qr.query(sql, new BeanListHandler<>(Goods.class), status);
        return list;
    }
    //全查询
    public List<Goods> selectAll() throws SQLException {
        String sql = "select * from goods";
        List<Goods> list = qr.query(sql, new BeanListHandler<>(Goods.class));
        return list;
    }
}